<?php
/**
 * Created by PhpStorm.
 * User: zhang
 * Date: 2018/4/28
 * Time: 19:50
 */
namespace app\common\model;

use think\Model;
use think\Db;
use think\Config;

class Mysql extends Model{

    private $db;

    function __construct()
    {
        error_reporting(E_ERROR | E_WARNING | E_PARSE);//规避一般错误
//        //$mysqlservername = "rm-bp16q94uh3l21n1ra.mysql.rds.aliyuncs.com:3456";//内网

////$mysqlservername = "rm-bp16q94uh3l21n1ra.mysql.rds.aliyuncs.com:3456";//外网
////        $mysqlusername = "topsydy";
////        $mysqlpassword = "Topsydy5082";
//        $mysqlservername="localhost:3306";
//        $mysqlusername = "root";
//        $mysqlpassword = "root";
//        $DBName = "topsydy";
//        $conn = @mysql_connect($mysqlservername, $mysqlusername, $mysqlpassword);
//        if (!$conn) {
//            die(echoJson(0, "数据库连接失败!"));
//        }
//        mysql_query("SET NAMES UTF8");
//        mysql_query("set character_set_client=utf8");
//        if (mysql_select_db($DBName) === null) {
//            die(echoJson(0, "数据库链接错误!"));
//        }

        Config::load(APP_PATH . 'common/config.php');
        $this->db = Db::connect(Config::get('mysql'));


    }

    /**
     * 查询语句查询是否有值（只查询一行）
     * @param str $sql 查询语句
     */
    function selectSQL($sql, $errmsg = '查询数据出错!') {
        $result = $this->selectSQLAll($sql)[0];
        return $result==NULL?false:$result;
    }


    function count($sql, $errmsg = '查询数据出错!'){

    }
    /**
     * 查询语句查询是否有值（只查询一行）
     * @param str $sql 查询语句
     */
    function selectSQLRow($sql, $errmsg = '查询数据出错!') {
        $result = $this->selectSQLAll($sql)[0];
        return $result==NULL?false:$result;
    }

    /**
     * 查询语句查询是否有值（查询所有）
     * @param str $sql 查询语句
     */
    function selectSQLAll($sql, $errmsg = '查询数据出错!') {

        $result = $this->db->query($sql);
        return $result[0]==0?false:$result;
    }
    /**
     * 插入语句
     * @param str $sql 查询语句
     */
    function insertSQL($sql, $errmsg = '插入数据出错!') {
        $result = $this->db->execute($sql);// or die(echoJson(0, $errmsg));

        return $result==0?false:true;
    }

    function insertSQLArray($table,$array){
        //$sql = "insert into"
        return $this->db->table($table)->insert($array);
    }
    /**
     * 删除语句
     * @param str $sql 查询语句
     */
    function deleteSQL($sql, $errmsg = '删除数据出错!') {
        $result = mysql_query($sql) or die(echoJson(0, $errmsg));
        if ($result) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 更新语句
     * @param str $sql 查询语句
     */
    function updateSQL($sql, $errmsg = '更新数据出错!') {
        $result = $this->db->execute($sql);// or die(echoJson(0, $errmsg));

        return $result==0?false:true;
    }

    /**
     * 开始事务
     */
    function beginTransactionSQL($errmsg = '开始事务出错!') {
        mysql_query("BEGIN") or die(echoJson(0, $errmsg));
        mysql_query("SET AUTOCOMMIT=0") or die(echoJson(0, $errmsg));
    }

    /**
     * 提交事务
     */
    function submitTransactionSQL($errmsg = '提交事务出错!') {
        mysql_query("COMMIT") or die(echoJson(0, $errmsg));
        $a = mysql_query("END"); //没有返回值
        mysql_query("SET AUTOCOMMIT=1") or die(echoJson(0, $errmsg));
    }

    /**
     * 滚回事务
     */
    function rollbackTransactionSQL($errmsg = '滚回事务出错!') {
        mysql_query("ROLLBACK") or die(echoJson(0, $errmsg));
        mysql_query("END");
        mysql_query("SET AUTOCOMMIT=1") or die(echoJson(0, $errmsg));
    }

    function selectSQLbyCustom($db,$customKey=[],$sql=null){
        return $this->selectSQLAllbyCustom($db,$customKey,$sql)[0];
    }

    function selectSQLAllbyCustom($db,$customKey=[],$sql=null,$page=null,$length=10){

        if($sql == null){
            if(count($customKey) == 0){
                if($page == null){
                    $data = $this->db->table($db)->select();
                }
                else{
                    $data = $this->db->table($db)->limit(($page-1)*$length,$length)->select();
                }
            }else{
                if($page == null){
                    $data = $this->db->table($db)->where($customKey)->select();
                }
                else{
                    $data = $this->db->table($db)->where($customKey)->limit(($page-1)*$length,$length)->select();
                }
            }
        }else{
            //var_dump($sql);
            $data = $this->db->query($sql);
        }
       
//        if($sql == null){
//            if(count($customKey) == 0){
//                $selectSql = "select * from " .$db;
//            }else{
//                $condition = null;
//                foreach ($customKey as $key=>$value){
//                    //echo "Key: $key; Value: $value<br />\n";
//                    if($condition==null){
//                        $condition = $condition." $key='$value'";
//                    }else{
//                        $condition = $condition." and $key='$value'";
//                    }
//                }
//
//                $selectSql = "select * from " .$db. " where " .$condition;
//            }
//        }else{
//            $selectSql = $sql;
//        }
//        $data = $this->selectSQLAll($selectSql);

        return $data;
    }

    function createSQLbyCustom($db,$saveSequence=[],$sql=null){
        //判断是否重复创建考试
//        if($this->getExamScheduleByCustom($saveSequence)){
//            echoJson(0, self::REPEAT_CREATE_EXAM);
//        }

        if($sql == null){
            $sqlKey = null;
            $sqlValue = null;

            foreach ($saveSequence as $key=>$value){
                if($sqlKey==null){
                    $sqlKey = $key;
                }else{
                    $sqlKey = $sqlKey . "," . $key;
                }


                if($sqlValue==null){
                    $sqlValue = "'".$value."'";
                }else{
                    $sqlValue = $sqlValue . ",'" . $value."'";
                }
            }

            $createSql = "insert into " .$db. "(".$sqlKey.") value "."(".$sqlValue.")";
        }else{
            $createSql = $sql;
        }

        return $this->insertSQL($createSql);
    }

    function deleteSQLbyCustom($db,$deleteCondition,$sql=null){
        if($sql==null){
            $data = $this->db->table($db)->where($deleteCondition)->delete();
        }else{
            $data = $this->db->execute($sql);
        }
        return $data;
    }

    function updateSQLbyCustom($db,$saveCondition,$saveSequence=[],$sql=null){
        $conditionSql = null;
        $SequencceSql = null;
        if($sql == null){
//            foreach ($saveCondition as $key=>$value){
//                //echo "Key: $key; Value: $value<br />\n";
//                if($conditionSql==null){
//                    $conditionSql = $conditionSql."$key='$value'";
//                }else{
//                    $conditionSql = $conditionSql." and $key='$value'";
//                }
//            }
//
//            foreach ($saveSequence as $key=>$value){
//                //echo "Key: $key; Value: $value<br />\n";
//                if($SequencceSql==null){
//                    $SequencceSql = $SequencceSql."$key='$value'";
//                }else{
//                    $SequencceSql = $SequencceSql.",$key='$value'";
//                }
//            }
//
//            $updateSql = "update " .$db. " set ".$SequencceSql." where " .$conditionSql;
            $data = $this->db->table($db)->where($saveCondition)->update($saveSequence);
        }else{
            $data = $this->db->execute($sql);
        }

        return $data;
    }

    function insertSQLbyCustom($db,$saveSequence=[],$sql=null){
        $examKey = null;
        $examValue = null;

        if($sql == null){
//            foreach ($saveSequence as $key=>$value){
//                if($examKey==null){
//                    $examKey = $key;
//                }else{
//                    $examKey = $examKey . "," . $key;
//                }
//                if($examValue==null){
//                    $examValue = $value;
//                }else{
//                    $examValue = $examValue . "," . $value;
//                }
//            }
//
//            $insertSql = "insert into " .$db. "(".$examKey.") value "."(".$examValue.")";
            $this->db->table($db)->startTrans();
            try{
                $data = $this->db->table($db)->insert($saveSequence);
                // 提交事务
                $this->db->table($db)->commit();
            } catch (\Exception $e) {
                // 回滚事务
                $this->db->table($db)->rollback();
                //echoJson(0, "error");
                return false;
            }

        }else{
            //$insertSql = $sql;
            $data = $this->db->execute($sql);
        }
        
        //var_dump($insertSql);

        return $data;
    }
}